// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 加载MD5模块
const md5 = require('md5');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: '',
  database: 'yimiao',
  connectionLimit: 20,
  charset: 'utf8'

  //新浪云配置参数
  // host     : process.env.MYSQL_HOST,
  //   port     : process.env.MYSQL_PORT,
  //   user     : process.env.ACCESSKEY,
  //   password : process.env.SECRETKEY,
  //   database : 'app_' + process.env.APPNAME,

  // connectionLimit: 5
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));

//用户注册接口
server.post('/register', (req, res) => {
  //console.log(md5('12345678'));
  // 获取用户名和密码信息
  let username = req.body.name;
  let password = req.body.password;
  let phone = req.body.tel;
  let nickname = req.body.nickname
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(id) AS count FROM  yimiao_sys_usr WHERE name=?';
  pool.query(sql, [username], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT  yimiao_sys_usr(name,nickname,password,tel) VALUES(?,?,MD5(?),?)';
      pool.query(sql, [username, nickname, password, phone], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});

// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let username = req.body.name;
  let password = req.body.password;
  // SQL语句
  let sql = 'SELECT * FROM  yimiao_sys_usr WHERE name=? AND password=MD5(?)';
  pool.query(sql, [username, password], (error, results) => {
    if (error) throw error;
    if (results.length == 0) { //登录失败
      res.send({ message: 'login failed', code: 201 });
    } else {                 //登录成功
      res.send({ message: 'ok', code: 200, result: results[0] });
    }
  });

});

//首页关于页面接口合集
server.get("/about", (req, res) => {
  let sql =
    "select id,tupian from shouye_about";
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});

server.get("/shouyenr", (req, res) => {
  let cid = req.query.nr_id;
  let sql =
    "select id,title,neirong from shouye_about_nr where nr_id=?";
  pool.query(sql, [cid], (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});

//首页图标与相关的信息
server.get("/tubiao", (req, res) => {
  let id = req.query.tb_id;
  let sql =
    "select id,m_title,ss_title,m_img from yimiao_tubiao where tb_id=?";
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});

server.get("/tb", (req, res) => {
  let id = req.query.tb_id;
  let sql =
    "select id,m_title,ss_title,m_img from yimiao_tb where tb_id=?";
  pool.query(sql, [id], (error, results) => {
    // console.log(results)
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});

//首页六宫格
server.get("/liugong", (req, res) => {
  let id = req.query.tb_id;
  let sql =
    "select m_title,ss_title,m_img from yimiao_tubiao where tb_id=?";
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});
//首页文章
server.get("/wz", (req, res) => {
  let sql =
    "select id,tupian,biaoti,xbiaoti,kanguo from yimiao_souye_wz";
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});
// 科普精选专题
server.get("/zhuanti", (req, res) => {
  let sql =
    "select id,guankan,biaoti,tupian,xiaobiao,yanse from yimiao_kepu_zhuanti";
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});
// 科普文章
server.get("/wenzhang", (req, res) => {
  let sql =
    "select id,tupian,biaoti,shijian from yimiao_kepu_wz";
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: results });
  });
});
// 预约信息录入接口
server.post("/yuyue", (req, res) => {
  let diqu = req.body.diqu;
  let rili = req.body.rili;
  let yname = req.body.yname;
  let phone = req.body.phone;
  let sex = req.body.sex;
  let yimiao = req.body.yimiao;
  let sql =
    "INSERT yimiao_yuyue(diqu,rili,yname,phone,sex,yimiao) VALUES(?,?,?,?,?,?)";
  pool.query(sql, [diqu, rili, yname, phone, sex, yimiao], (error, results) => {
    if (error) throw error;
    res.send({ message: "ok", code: 200, results: results });
  });
});

// 页面详情接口
server.get("/article", (req, res) => {
  let id = req.query.id;
  let sql =
    "select image,title,titleone,nrone,photo,nrtwo,source from shouye_xiangqing where id=?";
  pool.query(sql, [id], (error, result) => {
    if (error) throw error;
    res.send({ message: "查询成功", code: 1, results: result[0] });
  });
});

// 用户反馈接口
server.post("/article", (req, res) => {
  let fankui = req.body.fankui;
  let sql =
    "INSERT user_fankui(fankui) VALUES(?)";
  pool.query(sql, [fankui], (error, results) => {
    if (error) throw error;
    res.send({ message: "ok", code: 200, results: results });
  });
});


// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('server is running...');
});